using System;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.Collections.Generic;
using CRM_SMS_MARKETING.DAL;
namespace GSM.BusinessLayer.DataLayer
{
	/// <summary>
	/// Data access layer class for Customer
	/// </summary>
	class CustomerSql : DataLayerBase 
	{

        #region Constructor

		/// <summary>
		/// Class constructor
		/// </summary>
		public CustomerSql()
		{
			// Nothing for now.
		}

        #endregion

        #region Public Methods

        public List<Customer> SelectCustomerByBirthday()
        {
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "[dbo].[Customer_Select_BirthDay]";
            sqlCommand.CommandType = CommandType.StoredProcedure;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;

            try
            {

                MainConnection.Open();

                IDataReader dataReader = sqlCommand.ExecuteReader();

                return PopulateObjectsFromReader(dataReader);

            }
            catch (Exception ex)
            {
                throw new Exception("Customer::Select All Customer Have Birthday Is Today::Error occured.", ex);
            }
            finally
            {
                MainConnection.Close();
                sqlCommand.Dispose();
            }


        }

        /// <summary>
        /// insert new row in the table
        /// </summary>
		/// <param name="businessObject">business object</param>
		/// <returns>true of successfully insert</returns>
		public bool Insert(Customer businessObject)
		{
			SqlCommand	sqlCommand = new SqlCommand();
			sqlCommand.CommandText = "dbo.[Customer_Insert]";
			sqlCommand.CommandType = CommandType.StoredProcedure;

			// Use connection object of base class
			sqlCommand.Connection = MainConnection;

			try
			{
                
				sqlCommand.Parameters.Add(new SqlParameter("@CustomerID", SqlDbType.Int, 4, ParameterDirection.Output, false, 0, 0, "", DataRowVersion.Proposed, businessObject.CustomerID));
				sqlCommand.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 50, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.FirstName));
				sqlCommand.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 50, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.LastName));
				sqlCommand.Parameters.Add(new SqlParameter("@CellPhone", SqlDbType.NVarChar, 20, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.CellPhone));
				sqlCommand.Parameters.Add(new SqlParameter("@Gender", SqlDbType.Bit, 1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Gender));
				sqlCommand.Parameters.Add(new SqlParameter("@Birthday",businessObject.Birthday));
                //				sqlCommand.Parameters.Add(new SqlParameter("@Birthday", SqlDbType.Text, 3, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Birthday));
				sqlCommand.Parameters.Add(new SqlParameter("@Addrress", SqlDbType.NVarChar, 100, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Addrress));
				sqlCommand.Parameters.Add(new SqlParameter("@District", SqlDbType.NVarChar, 20, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.District));
				sqlCommand.Parameters.Add(new SqlParameter("@City", SqlDbType.NVarChar, 50, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.City));
				sqlCommand.Parameters.Add(new SqlParameter("@Phone", SqlDbType.NVarChar, 20, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Phone));
				sqlCommand.Parameters.Add(new SqlParameter("@Company", SqlDbType.NVarChar, 50, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Company));
				sqlCommand.Parameters.Add(new SqlParameter("@Email", SqlDbType.NVarChar, 50, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Email));
				sqlCommand.Parameters.Add(new SqlParameter("@User", SqlDbType.NVarChar, 50, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.User));
				sqlCommand.Parameters.Add(new SqlParameter("@Deleted", SqlDbType.Bit, 1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Deleted));

								
				MainConnection.Open();
				
				sqlCommand.ExecuteNonQuery();
                businessObject.CustomerID = (int)sqlCommand.Parameters["@CustomerID"].Value;

				return true;
			}
			catch(Exception ex)
			{				
				throw new Exception("Customer::Insert::Error occured.", ex);
			}
			finally
			{			
				MainConnection.Close();
				sqlCommand.Dispose();
			}
		}

         /// <summary>
        /// update row in the table
        /// </summary>
        /// <param name="businessObject">business object</param>
        /// <returns>true for successfully updated</returns>
        public bool Update(Customer businessObject)
        {
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "dbo.[Customer_Update]";
            sqlCommand.CommandType = CommandType.StoredProcedure;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;

            try
            {
                
				sqlCommand.Parameters.Add(new SqlParameter("@CustomerID", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.CustomerID));
				sqlCommand.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 50, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.FirstName));
				sqlCommand.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 50, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.LastName));
				sqlCommand.Parameters.Add(new SqlParameter("@CellPhone", SqlDbType.NVarChar, 20, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.CellPhone));
				sqlCommand.Parameters.Add(new SqlParameter("@Gender", SqlDbType.Bit, 1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Gender));
				sqlCommand.Parameters.Add(new SqlParameter("@Birthday", businessObject.Birthday));
				sqlCommand.Parameters.Add(new SqlParameter("@Addrress", SqlDbType.NVarChar, 100, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Addrress));
				sqlCommand.Parameters.Add(new SqlParameter("@District", SqlDbType.NVarChar, 20, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.District));
				sqlCommand.Parameters.Add(new SqlParameter("@City", SqlDbType.NVarChar, 50, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.City));
				sqlCommand.Parameters.Add(new SqlParameter("@Phone", SqlDbType.NVarChar, 20, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Phone));
				sqlCommand.Parameters.Add(new SqlParameter("@Company", SqlDbType.NVarChar, 50, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Company));
				sqlCommand.Parameters.Add(new SqlParameter("@Email", SqlDbType.NVarChar, 50, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Email));
				sqlCommand.Parameters.Add(new SqlParameter("@User", SqlDbType.NVarChar, 50, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.User));
				sqlCommand.Parameters.Add(new SqlParameter("@Deleted", SqlDbType.Bit, 1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, businessObject.Deleted));

                
                MainConnection.Open();

                sqlCommand.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                throw new Exception("Customer::Update::Error occured.", ex);
            }
            finally
            {
                MainConnection.Close();
                sqlCommand.Dispose();
            }
        }

        /// <summary>
        /// Select by primary key
        /// </summary>
        /// <param name="keys">primary keys</param>
        /// <returns>Customer business object</returns>
        public Customer SelectByPrimaryKey(CustomerKeys keys)
        {
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "dbo.[Customer_SelectByPrimaryKey]";
            sqlCommand.CommandType = CommandType.StoredProcedure;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;

            try
            {

				sqlCommand.Parameters.Add(new SqlParameter("@CustomerID", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, keys.CustomerID));

                
                MainConnection.Open();

                IDataReader dataReader = sqlCommand.ExecuteReader();

                if (dataReader.Read())
                {
                    Customer businessObject = new Customer();

                    PopulateBusinessObjectFromReader(businessObject, dataReader);

                    return businessObject;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Customer::SelectByPrimaryKey::Error occured.", ex);
            }
            finally
            {             
                MainConnection.Close();
                sqlCommand.Dispose();
            }

        }

        /// <summary>
        /// Select all rescords
        /// </summary>
        /// <returns>list of Customer</returns>
        public List<Customer> SelectAll()
        {
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "dbo.[Customer_SelectAll]";
            sqlCommand.CommandType = CommandType.StoredProcedure;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;

            try
            {
                
                MainConnection.Open();

                IDataReader dataReader = sqlCommand.ExecuteReader();

                return PopulateObjectsFromReader(dataReader);

            }
            catch (Exception ex)
            {                
                throw new Exception("Customer::SelectAll::Error occured.", ex);
            }
            finally
            {
                MainConnection.Close();
                sqlCommand.Dispose();
            }

        }

        /// <summary>
        /// Select records by field
        /// </summary>
        /// <param name="fieldName">name of field</param>
        /// <param name="value">value of field</param>
        /// <returns>list of Customer</returns>
        public List<Customer> SelectByField(string fieldName, object value)
        {

            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "dbo.[Customer_SelectByField]";
            sqlCommand.CommandType = CommandType.StoredProcedure;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;

            try
            {

                sqlCommand.Parameters.Add(new SqlParameter("@FieldName", fieldName));
                sqlCommand.Parameters.Add(new SqlParameter("@Value", value));

                MainConnection.Open();
                
                IDataReader dataReader = sqlCommand.ExecuteReader();

                return PopulateObjectsFromReader(dataReader);

            }
            catch (Exception ex)
            {
                throw new Exception("Customer::SelectByField::Error occured.", ex);
            }
            finally
            {

                MainConnection.Close();
                sqlCommand.Dispose();
            }

        }

        /// <summary>
        /// Delete by primary key
        /// </summary>
        /// <param name="keys">primary keys</param>
        /// <returns>true for successfully deleted</returns>
        public bool Delete(CustomerKeys keys)
        {
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "dbo.[Customer_DeleteByPrimaryKey]";
            sqlCommand.CommandType = CommandType.StoredProcedure;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;

            try
            {

				sqlCommand.Parameters.Add(new SqlParameter("@CustomerID", SqlDbType.Int, 4, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, keys.CustomerID));


                MainConnection.Open();

                sqlCommand.ExecuteNonQuery();

                return true;
            }
            catch (Exception ex)
            {
                throw new Exception("Customer::DeleteByKey::Error occured.", ex);
            }
            finally
            {                
                MainConnection.Close();
                sqlCommand.Dispose();
            }

        }

        /// <summary>
        /// Delete records by field
        /// </summary>
        /// <param name="fieldName">name of field</param>
        /// <param name="value">value of field</param>
        /// <returns>true for successfully deleted</returns>
        public bool DeleteByField(string fieldName, object value)
        {
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "dbo.[Customer_DeleteByField]";
            sqlCommand.CommandType = CommandType.StoredProcedure;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;

            try
            {

                sqlCommand.Parameters.Add(new SqlParameter("@FieldName", fieldName));
                sqlCommand.Parameters.Add(new SqlParameter("@Value", value));
                
                MainConnection.Open();

                sqlCommand.ExecuteNonQuery();

                return true;

            }
            catch (Exception ex)
            {                
                throw new Exception("Customer::DeleteByField::Error occured.", ex);
            }
            finally
            {             
                MainConnection.Close();
                sqlCommand.Dispose();
            }

        }

        #endregion
        /// <summary>
        /// Get the records
        /// </summary>
        /// <returns>true for successfully get</returns>

        public object GetListCity()
        {
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "Select DISTINCT City from Customer";
            sqlCommand.CommandType = CommandType.Text;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;
            try
            {

          
                    DataTable table = new DataTable();
                    SqlConnection con = sqlCommand.Connection;
                    SqlDataAdapter dataAdapter = new SqlDataAdapter(sqlCommand.CommandText, con);
                    dataAdapter.Fill(table);
                    List<string> City= new List<string>();
                    for (int i = 0; i < table.Rows.Count; i++)
                    {
                        City.Add(table.Rows[i]["City"].ToString());
                    }
                    return City;
                
           
           
            }
            catch (Exception ex)
            {
                throw new Exception("Customer::Get List City::Error occured.", ex);
            }
            finally
            {
                MainConnection.Close();
                sqlCommand.Dispose();
            }

        }
        public object GetLastReCordID()
        {
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "Select CustomerID from Customer where CustomerID=IDENT_CURRENT('Customer')";
            sqlCommand.CommandType = CommandType.Text;

            // Use connection object of base class
            sqlCommand.Connection = MainConnection;
            try
            {

                MainConnection.Open();

                return sqlCommand.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw new Exception("Customer::Get Last CustomerID::Error occured.", ex);
            }
            finally
            {
                MainConnection.Close();
                sqlCommand.Dispose();
            }

        }

        #region Private Methods

        /// <summary>
        /// Populate business object from data reader
        /// </summary>
        /// <param name="businessObject">business object</param>
        /// <param name="dataReader">data reader</param>
        internal void PopulateBusinessObjectFromReader(Customer businessObject, IDataReader dataReader)
        {


				businessObject.CustomerID = dataReader.GetInt32(dataReader.GetOrdinal(Customer.CustomerFields.CustomerID.ToString()));

				if (!dataReader.IsDBNull(dataReader.GetOrdinal(Customer.CustomerFields.FirstName.ToString())))
				{
					businessObject.FirstName = dataReader.GetString(dataReader.GetOrdinal(Customer.CustomerFields.FirstName.ToString()));
				}

				if (!dataReader.IsDBNull(dataReader.GetOrdinal(Customer.CustomerFields.LastName.ToString())))
				{
					businessObject.LastName = dataReader.GetString(dataReader.GetOrdinal(Customer.CustomerFields.LastName.ToString()));
				}

				if (!dataReader.IsDBNull(dataReader.GetOrdinal(Customer.CustomerFields.CellPhone.ToString())))
				{
					businessObject.CellPhone = dataReader.GetString(dataReader.GetOrdinal(Customer.CustomerFields.CellPhone.ToString()));
				}

				if (!dataReader.IsDBNull(dataReader.GetOrdinal(Customer.CustomerFields.Gender.ToString())))
				{
					businessObject.Gender = dataReader.GetBoolean(dataReader.GetOrdinal(Customer.CustomerFields.Gender.ToString()));
				}

				if (!dataReader.IsDBNull(dataReader.GetOrdinal(Customer.CustomerFields.Birthday.ToString())))
				{
					businessObject.Birthday = dataReader.GetDateTime(dataReader.GetOrdinal(Customer.CustomerFields.Birthday.ToString()));
				}

				if (!dataReader.IsDBNull(dataReader.GetOrdinal(Customer.CustomerFields.Addrress.ToString())))
				{
					businessObject.Addrress = dataReader.GetString(dataReader.GetOrdinal(Customer.CustomerFields.Addrress.ToString()));
				}

				if (!dataReader.IsDBNull(dataReader.GetOrdinal(Customer.CustomerFields.District.ToString())))
				{
					businessObject.District = dataReader.GetString(dataReader.GetOrdinal(Customer.CustomerFields.District.ToString()));
				}

				if (!dataReader.IsDBNull(dataReader.GetOrdinal(Customer.CustomerFields.City.ToString())))
				{
					businessObject.City = dataReader.GetString(dataReader.GetOrdinal(Customer.CustomerFields.City.ToString()));
				}

				if (!dataReader.IsDBNull(dataReader.GetOrdinal(Customer.CustomerFields.Phone.ToString())))
				{
					businessObject.Phone = dataReader.GetString(dataReader.GetOrdinal(Customer.CustomerFields.Phone.ToString()));
				}

				if (!dataReader.IsDBNull(dataReader.GetOrdinal(Customer.CustomerFields.Company.ToString())))
				{
					businessObject.Company = dataReader.GetString(dataReader.GetOrdinal(Customer.CustomerFields.Company.ToString()));
				}

				if (!dataReader.IsDBNull(dataReader.GetOrdinal(Customer.CustomerFields.Email.ToString())))
				{
					businessObject.Email = dataReader.GetString(dataReader.GetOrdinal(Customer.CustomerFields.Email.ToString()));
				}

				if (!dataReader.IsDBNull(dataReader.GetOrdinal(Customer.CustomerFields.User.ToString())))
				{
					businessObject.User = dataReader.GetString(dataReader.GetOrdinal(Customer.CustomerFields.User.ToString()));
				}

				businessObject.Deleted = dataReader.GetBoolean(dataReader.GetOrdinal(Customer.CustomerFields.Deleted.ToString()));


        }

        /// <summary>
        /// Populate business objects from the data reader
        /// </summary>
        /// <param name="dataReader">data reader</param>
        /// <returns>list of Customer</returns>
        internal List<Customer> PopulateObjectsFromReader(IDataReader dataReader)
        {

            List<Customer> list = new List<Customer>();

            while (dataReader.Read())
            {
                Customer businessObject = new Customer();
                PopulateBusinessObjectFromReader(businessObject, dataReader);
                list.Add(businessObject);
            }
            return list;

        }

        #endregion

	}
}
